package com.phison.fenestrapro.dashboard.util;

import java.io.Serializable;
import java.security.NoSuchAlgorithmException;
import java.security.spec.InvalidKeySpecException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import com.vaadin.data.Container.Filter;
import com.vaadin.data.Item;
import com.vaadin.data.util.filter.And;
import com.vaadin.data.util.filter.Like;
import com.vaadin.data.util.sqlcontainer.RowId;
import com.vaadin.data.util.sqlcontainer.SQLContainer;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.connection.SimpleJDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate.RowIdChangeEvent;
import com.vaadin.data.util.sqlcontainer.query.TableQuery;

/**
 *
 * 
 * @author Niall O'Hara
 *
 */
@SuppressWarnings("serial")
public class DatabaseHelper implements Serializable, GlobalConstants {

    private JDBCConnectionPool connectionPool = null;
    
    private SQLContainer companyContainer = null;
    private SQLContainer userContainer = null;
    private SQLContainer authenticationContainer = null;
    private SQLContainer projectContainer = null;
    private SQLContainer login_historyContainer = null;
    private SQLContainer user_has_projectContainer = null;
    private SQLContainer buildingContainer = null;
    private SQLContainer facadeContainer = null;
    private SQLContainer floorContainer = null;
    private SQLContainer building_has_facadeContainer = null;
    private SQLContainer project_has_buildingContainer = null;
    private SQLContainer windowContainer = null;
    private SQLContainer building_has_floorContainer = null;
    private SQLContainer user_has_buildingContainer = null;
    
    private int newUserID = -1;
    @SuppressWarnings("unused")
	private int newCompanyID = -1;
    private int newProjectID = -1;
    private int newBuildingID = -1;
    private int newFacadeID = -1;
    private int newFloorID = -1;
    
    public DatabaseHelper() {
        initConnectionPool();
        initContainers();
        //addNewCompany();
    }

    //private void addNewCompany() {}
    
    private void initConnectionPool() {
    	try {
            connectionPool = new SimpleJDBCConnectionPool(
                     "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/fenestravaadin",
                     "fenestraVaadin", "VAD_f3n3strapr0_", 1, 4);
    		
    		/*connectionPool = new SimpleJDBCConnectionPool(
                    "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/fenestravaadin",
                    "root", "", 1, 4);*/
    		
        } catch (SQLException e) {
            // TODO Auto-generated catch block
        	System.err.println("Error in Connection Pool");
            e.printStackTrace();
        }
    }
    
    public void destroyConnectionPool() {
    	connectionPool.destroy();
    	connectionPool = null;
    }

    //q2.setVersionColumn("user_id"); // to sync with multiple users
    private void initContainers() {
        try {
            TableQuery q1 = new TableQuery("company", connectionPool);
            companyContainer = new SQLContainer(q1);
        	companyContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newCompanyID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
            
            TableQuery q2 = new TableQuery("user", connectionPool);
            userContainer = new SQLContainer(q2);
            userContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newUserID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
        	
            TableQuery q3 = new TableQuery("authentication", connectionPool);
            authenticationContainer = new SQLContainer(q3);
            
            TableQuery q4 = new TableQuery("project", connectionPool);
            projectContainer = new SQLContainer(q4);
            projectContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newProjectID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
            
            TableQuery q5 = new TableQuery("login_history", connectionPool);
            login_historyContainer = new SQLContainer(q5);
            
            TableQuery q6 = new TableQuery("user_has_project", connectionPool);
            user_has_projectContainer = new SQLContainer(q6);
            
            TableQuery q7 = new TableQuery("building", connectionPool);
            buildingContainer = new SQLContainer(q7);
            buildingContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newBuildingID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
            
            TableQuery q8 = new TableQuery("facade", connectionPool);
            facadeContainer = new SQLContainer(q8);
            facadeContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newFacadeID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
            
            TableQuery q9 = new TableQuery("floor", connectionPool);
            floorContainer = new SQLContainer(q9);
            floorContainer.addListener(
        			new QueryDelegate.RowIdChangeListener() {
					@Override
					public void rowIdChange(RowIdChangeEvent event) {
						newFloorID = Integer.parseInt(event.getNewRowId().toString());
					}
    		    });
            
            TableQuery q10 = new TableQuery("building_has_facade", connectionPool);
            building_has_facadeContainer = new SQLContainer(q10);
            
            TableQuery q11 = new TableQuery("project_has_building", connectionPool);
            project_has_buildingContainer = new SQLContainer(q11);
            
            TableQuery q12 = new TableQuery("window", connectionPool);
            windowContainer = new SQLContainer(q12);
            
            TableQuery q13 = new TableQuery("building_has_floor", connectionPool);
            building_has_floorContainer = new SQLContainer(q13);
            
            TableQuery q14 = new TableQuery("user_has_building", connectionPool);
            user_has_buildingContainer = new SQLContainer(q14);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Adds a new company to the container and commits changes to the database.
     * 
     * @param name
     * 
     * @return true if the company was added successfully
     */
    public boolean addCompany(String name) {
    	
    	Filter filter = new Like("name", name, false);
    	companyContainer.addContainerFilter(filter);
    	Object id = companyContainer.getIdByIndex(0);
    	companyContainer.removeContainerFilter(filter);
    	
    	boolean returnValue = false;
    	
    	if (id == null) {
	    	id = companyContainer.addItem();
	    	companyContainer.getContainerProperty(id, "name").setValue(name);
	        try {
	        	companyContainer.commit();
	        	returnValue = true;
	        } catch (UnsupportedOperationException e) {
	            e.printStackTrace();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
    	} else {
    		System.out.println("Company " + name + " already exists");
    	}
    	
        return returnValue;
    }
    
    /**
     * Adds a new user project to the container and commits changes to the database.
     * 
     * @param user_id
     * @param project_id
     * 
     * @return true if the user project was added successfully
     */
    public boolean addUserProject(int user_id, int project_id) {
    	Object id = user_has_projectContainer.addItem();
    	user_has_projectContainer.getContainerProperty(id, "user_id").setValue(user_id);
    	user_has_projectContainer.getContainerProperty(id, "project_id").setValue(project_id);
        try {
        	user_has_projectContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new project to the container and commits changes to the database.
     * 
     * @param company_id
     * @param name
     * @param description
     * @param reference_number
     * @param longitude
     * @param latitude
     * @param location_setting
     * 
     * @return true if the project was added successfully
     */
    public boolean addProject(int company_id, String name, String description, String reference_number, double longitude, double latitude, String location_setting) {
    	Object id = projectContainer.addItem();
    	projectContainer.getContainerProperty(id, "company_id").setValue(company_id);
    	projectContainer.getContainerProperty(id, "name").setValue(name);
    	projectContainer.getContainerProperty(id, "description").setValue(description);
    	projectContainer.getContainerProperty(id, "reference_number").setValue(reference_number);
    	projectContainer.getContainerProperty(id, "longitude").setValue(longitude);
    	projectContainer.getContainerProperty(id, "latitude").setValue(latitude);
    	projectContainer.getContainerProperty(id, "location_setting").setValue(location_setting);
        try {
        	projectContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public Object getProjectValue(int project_id, String field) {
    	Filter filter = new Like("project_id", String.valueOf(project_id));
    	projectContainer.addContainerFilter(filter);
    	Object id = projectContainer.getIdByIndex(0);
    	projectContainer.removeContainerFilter(filter);
        return projectContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    public Item getProjectValues(int project_id) {
    	Filter filter = new Like("project_id", String.valueOf(project_id));
    	projectContainer.addContainerFilter(filter);
    	Object id = projectContainer.getIdByIndex(0);
    	projectContainer.removeContainerFilter(filter);
        return projectContainer.getItem(id);
    }
    
    public Object getBuildingFacadeID(int building_id, int facadeNum) {
    	Filter filter = new Like("building_id", String.valueOf(building_id));
    	building_has_facadeContainer.addContainerFilter(filter);
    	RowId[] facadeIDs = building_has_facadeContainer.getItemIds().toArray(new RowId[building_has_facadeContainer.size()]);
    	building_has_facadeContainer.removeContainerFilter(filter);
        return facadeIDs[facadeNum].getId()[1];
    }
    
    public Object getBuildingFloorID(int building_id, int floorNum) {
    	Filter filter = new Like("building_id", String.valueOf(building_id));
    	building_has_floorContainer.addContainerFilter(filter);
    	RowId[] floorIDs = building_has_floorContainer.getItemIds().toArray(new RowId[building_has_floorContainer.size()]);
    	building_has_floorContainer.removeContainerFilter(filter);
    	Object toReturn = null;
    	try {
    		toReturn = floorIDs[floorNum].getId()[1];
    	} catch (ArrayIndexOutOfBoundsException e) {
    		e.printStackTrace();
    		this.addFloor(floorNames[floorNum], floorNum);
    		this.addBuildingFloor(building_id, newFloorID );
    		toReturn = getBuildingFloorID(building_id, floorNum);
    	}
        return toReturn;
    }
    
    public int[] getUserProjectIDs(int user_id) {
    	Filter filter = new Like("user_id", String.valueOf(user_id));
    	user_has_projectContainer.addContainerFilter(filter);
    	int[] toReturn = new int[user_has_projectContainer.size()];
    	for (int i = 0; i < user_has_projectContainer.size(); i++) {
    	    Object id = user_has_projectContainer.getIdByIndex(i);
    	    toReturn[i] = (Integer) user_has_projectContainer.getItem(id).getItemProperty("project_id").getValue();
    	}
    	user_has_projectContainer.removeContainerFilter(filter);
    	return toReturn;
    }
    
    public int getUserID(String username) {
		Filter filter = new Like("username", username, false);
		authenticationContainer.addContainerFilter(filter);
		Object id = authenticationContainer.getIdByIndex(0);
		authenticationContainer.removeContainerFilter(filter);
		return (Integer) authenticationContainer.getItem(id).getItemProperty("user_id").getValue();
    }
    
    public Object getUserValue(int user_id, String field) {
		Filter filter = new Like("user_id", String.valueOf(user_id));
		userContainer.addContainerFilter(filter);
		Object id = userContainer.getIdByIndex(0);
		userContainer.removeContainerFilter(filter);
		return userContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    public Object getCompanyValue(int company_id, String field) {
		Filter filter = new Like("company_id", String.valueOf(company_id));
		companyContainer.addContainerFilter(filter);
		Object id = companyContainer.getIdByIndex(0);
		companyContainer.removeContainerFilter(filter);
		return companyContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    public Object getWindowValue(int facade_id, int floor_id, String field) {
    	Filter filter = new And(new Like("facade_id", String.valueOf(facade_id)), 
    			new Like("floor_id", String.valueOf(floor_id)));
    	windowContainer.addContainerFilter(filter);
    	Object id = windowContainer.getIdByIndex(0);
    	windowContainer.removeContainerFilter(filter);
    	Object toReturn = null;
    	try {
    		toReturn = windowContainer.getItem(id).getItemProperty(field).getValue();
    	} catch (NullPointerException e) {
    		e.printStackTrace();
    		this.addWindow(2.5d, 1.2d, 0.1d, facade_id, floor_id);
    		toReturn = getWindowValue(facade_id, floor_id, field);
    	}
        return toReturn;
    }
    
    public Item getWindowValues(int facade_id, int floor_id) {
    	Filter filter = new And(new Like("facade_id", String.valueOf(facade_id)), 
    			new Like("floor_id", String.valueOf(floor_id)));
    	windowContainer.addContainerFilter(filter);
    	Object id = windowContainer.getIdByIndex(0);
    	windowContainer.removeContainerFilter(filter);
    	Item toReturn = null;
    	try {
    		toReturn = windowContainer.getItem(id);
    	} catch (NullPointerException e) {
    		e.printStackTrace();
    		this.addWindow(2.5d, 1.2d, 0.1d, facade_id, floor_id);
    		toReturn = getWindowValues(facade_id, floor_id);
    	}
        return toReturn;
    }
    
    public Object getFacadeValue(int facade_id, String field) {
    	Filter filter = new Like("facade_id", String.valueOf(facade_id));
    	facadeContainer.addContainerFilter(filter);
    	Object id = facadeContainer.getIdByIndex(0);
    	facadeContainer.removeContainerFilter(filter);
        return facadeContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    public Item getFacadeValues(int facade_id) {
    	Filter filter = new Like("facade_id", String.valueOf(facade_id));
    	facadeContainer.addContainerFilter(filter);
    	Object id = facadeContainer.getIdByIndex(0);
    	facadeContainer.removeContainerFilter(filter);
        return facadeContainer.getItem(id);
    }
    
    public Object getBuildingValue(int building_id, String field) {
    	Filter filter = new Like("building_id", String.valueOf(building_id));
    	buildingContainer.addContainerFilter(filter);
    	Object id = buildingContainer.getIdByIndex(0);
    	buildingContainer.removeContainerFilter(filter);
        return buildingContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    public Item getBuildingValues(int building_id) {
    	Filter filter = new Like("building_id", String.valueOf(building_id));
    	buildingContainer.addContainerFilter(filter);
    	Object id = buildingContainer.getIdByIndex(0);
    	buildingContainer.removeContainerFilter(filter);
        return buildingContainer.getItem(id);
    }
    
    public Object getProjectBuildingValue(int project_id, String field) {
    	Filter filter = new Like("project_id", String.valueOf(project_id));
    	project_has_buildingContainer.addContainerFilter(filter);
    	Object id = project_has_buildingContainer.getIdByIndex(0);
    	project_has_buildingContainer.removeContainerFilter(filter);
        return project_has_buildingContainer.getItem(id).getItemProperty(field).getValue();
    }
    
    /**
     * Adds a new project to the container and commits changes to the database.
     * 
     * @param building_id
     * @param field
     * @param value
     * 
     * @return true if the project was added successfully
     */
    public boolean setProjectValue(int project_id, String field, Object value) {
    	Filter filter = new Like("project_id", String.valueOf(project_id));
    	projectContainer.addContainerFilter(filter);
    	Object id = projectContainer.getIdByIndex(0);
    	projectContainer.removeContainerFilter(filter);
    	projectContainer.getContainerProperty(id, field).setValue(value);
    	projectContainer.getContainerProperty(id, "last_update").setValue(new Timestamp(new Date().getTime()));
        try {
        	projectContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new project building to the container and commits changes to the database.
     * 
     * @param project_id
     * @param building_id
     * 
     * @return true if the project building was added successfully
     */
    public boolean addProjectBuilding(int project_id, int building_id) {
    	Object id = project_has_buildingContainer.addItem();
    	project_has_buildingContainer.getContainerProperty(id, "project_id").setValue(project_id);
    	project_has_buildingContainer.getContainerProperty(id, "building_id").setValue(building_id);
        try {
        	project_has_buildingContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    
    /**
     * Adds a new project building to the container and commits changes to the database.
     * 
     * @param project_id
     * @param building_id
     * 
     * @return true if the project building was added successfully
     */
    public boolean addLoginHistory(int user_id, String ip, String user_agent, int browser_width, int browser_height) {
    	Object id = login_historyContainer.addItem();
    	login_historyContainer.getContainerProperty(id, "user_id").setValue(user_id);
    	login_historyContainer.getContainerProperty(id, "ip").setValue(ip);
    	login_historyContainer.getContainerProperty(id, "user_agent").setValue(user_agent);
    	login_historyContainer.getContainerProperty(id, "browser_width").setValue(browser_width);
    	login_historyContainer.getContainerProperty(id, "browser_height").setValue(browser_height);
        try {
        	login_historyContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    
    /**
     * Adds a new building to the container and commits changes to the database.
     * 
     * @param building_id
     * @param field
     * @param value
     * 
     * @return true if the building was added successfully
     */
    public boolean setBuildingValue(int building_id, String field, Object value) {
    	Filter filter = new Like("building_id", String.valueOf(building_id));
    	buildingContainer.addContainerFilter(filter);
    	Object id = buildingContainer.getIdByIndex(0);
    	buildingContainer.removeContainerFilter(filter);
    	buildingContainer.getContainerProperty(id, field).setValue(value);
    	buildingContainer.getContainerProperty(id, "last_update").setValue(new Timestamp(new Date().getTime()));
        try {
        	buildingContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    
    /**
     * Adds a new window to the container and commits changes to the database.
     * 
     * @param facade_id
     * @param floor_id
     * @param field
     * @param value
     * 
     * @return true if the window was added successfully
     */
    public boolean setWindowValue(int facade_id, int floor_id, String field, Object value) {
    	Filter filter = new And(new Like("facade_id", String.valueOf(facade_id)), 
    			new Like("floor_id", String.valueOf(floor_id)));
    	windowContainer.addContainerFilter(filter);
    	Object id = windowContainer.getIdByIndex(0);
    	windowContainer.removeContainerFilter(filter);
    	windowContainer.getContainerProperty(id, field).setValue(value);
    	windowContainer.getContainerProperty(id, "last_update").setValue(new Timestamp(new Date().getTime()));
        try {
        	windowContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new building to the container and commits changes to the database.
     *
     * 
     * @return true if the building was added successfully
     */
    public boolean addBuilding(String facade_type, String building_type, double length, double width, int storeys, double overall_building_height,
    		double floor_to_floor, double floor_to_ceiling, String orientation, double u_value_roof,
    		double u_value_ground_floor, double u_value_walls, double u_value_glazing, double wall_thickness, String reflectance_interior_surfaces) {
    	Object id = buildingContainer.addItem();
    	buildingContainer.getContainerProperty(id, "facade_type").setValue(facade_type);
    	buildingContainer.getContainerProperty(id, "building_type").setValue(building_type);
    	buildingContainer.getContainerProperty(id, "length").setValue(length);
    	buildingContainer.getContainerProperty(id, "width").setValue(width);
    	buildingContainer.getContainerProperty(id, "storeys").setValue(storeys);
    	buildingContainer.getContainerProperty(id, "overall_building_height").setValue(overall_building_height);
    	buildingContainer.getContainerProperty(id, "floor_to_floor").setValue(floor_to_floor);
    	buildingContainer.getContainerProperty(id, "floor_to_ceiling").setValue(floor_to_ceiling);
    	buildingContainer.getContainerProperty(id, "orientation").setValue(orientation);
    	buildingContainer.getContainerProperty(id, "facade_performance").setValue(null);
    	buildingContainer.getContainerProperty(id, "u_value_roof").setValue(u_value_roof);
    	buildingContainer.getContainerProperty(id, "u_value_ground_floor").setValue(u_value_ground_floor);
    	buildingContainer.getContainerProperty(id, "u_value_walls").setValue(u_value_walls);
    	buildingContainer.getContainerProperty(id, "u_value_glazing").setValue(u_value_glazing);
    	buildingContainer.getContainerProperty(id, "overall_percent_glazing").setValue(null);
    	buildingContainer.getContainerProperty(id, "wall_thickness").setValue(wall_thickness);
    	buildingContainer.getContainerProperty(id, "reflectance_interior_surfaces").setValue(reflectance_interior_surfaces);
        try {
        	buildingContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new building facade to the container and commits changes to the database.
     * 
     * @param building_id
     * @param facade_id
     * 
     * @return true if the building facade was added successfully
     */
    public boolean addBuildingFacade(int building_id, int facade_id) {
    	Object id = building_has_facadeContainer.addItem();
    	building_has_facadeContainer.getContainerProperty(id, "building_id").setValue(building_id);
    	building_has_facadeContainer.getContainerProperty(id, "facade_id").setValue(facade_id);
        try {
        	building_has_facadeContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new building floor to the container and commits changes to the database.
     * 
     * @param building_id
     * @param floor_id
     * 
     * @return true if the building floor was added successfully
     */
    public boolean addBuildingFloor(int building_id, int floor_id) {
    	Object id = building_has_floorContainer.addItem();
    	building_has_floorContainer.getContainerProperty(id, "building_id").setValue(building_id);
    	building_has_floorContainer.getContainerProperty(id, "floor_id").setValue(floor_id);
        try {
        	building_has_floorContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new facade to the container and commits changes to the database.
     *
     * @param facade_number
     * @param glazed_percentage
     * @param glazed_area_lock
     * @param glazing_type
     * @param glazing_spec
     * @param percentage_frame
     * @param window_shading_type
     * @param window_config
     * @param window_shading_depth
     * @param shading_angle
     * @param louvre_colour
     * @param slat_angle
     * @param surroundings_avg_height
     * @param surroundings_avg_distance
     * 
     * @return true if the facade was added successfully
     */
    public boolean addFacade(int facade_number, double glazed_percentage, boolean glazed_area_lock, String glazing_type, String glazing_spec, double percentage_frame, 
    		String window_shading_type, String window_config, double window_shading_depth, String shading_angle, String louvre_colour, String slat_angle, 
    		double surroundings_avg_height, double surroundings_avg_distance) {
    	Object id = facadeContainer.addItem();
    	facadeContainer.getContainerProperty(id, "facade_number").setValue(facade_number);
    	facadeContainer.getContainerProperty(id, "glazed_percentage").setValue(glazed_percentage);
    	facadeContainer.getContainerProperty(id, "glazed_area_lock").setValue(glazed_area_lock);
    	facadeContainer.getContainerProperty(id, "glazing_type").setValue(glazing_type);
    	facadeContainer.getContainerProperty(id, "glazing_spec").setValue(glazing_spec);
    	facadeContainer.getContainerProperty(id, "percentage_frame").setValue(percentage_frame);
    	facadeContainer.getContainerProperty(id, "window_shading_type").setValue(window_shading_type);
    	facadeContainer.getContainerProperty(id, "window_config").setValue(window_config);
    	facadeContainer.getContainerProperty(id, "window_shading_depth").setValue(window_shading_depth);
    	facadeContainer.getContainerProperty(id, "shading_angle").setValue(shading_angle);
    	facadeContainer.getContainerProperty(id, "louvre_colour").setValue(louvre_colour);
    	facadeContainer.getContainerProperty(id, "slat_angle").setValue(slat_angle);
    	facadeContainer.getContainerProperty(id, "surroundings_avg_height").setValue(surroundings_avg_height);
    	facadeContainer.getContainerProperty(id, "surroundings_avg_distance").setValue(surroundings_avg_distance);
        try {
        	facadeContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new facade to the container and commits changes to the database.
     * 
     * @param facade_id
     * @param field
     * @param value
     * 
     * @return true if the facade was added successfully
     */
    public boolean setFacadeValue(int facade_id, String field, Object value) {
    	Filter filter = new Like("facade_id", String.valueOf(facade_id));
    	facadeContainer.addContainerFilter(filter);
    	Object id = facadeContainer.getIdByIndex(0);
    	facadeContainer.removeContainerFilter(filter);
    	facadeContainer.getContainerProperty(id, field).setValue(value);
    	facadeContainer.getContainerProperty(id, "last_update").setValue(new Timestamp(new Date().getTime()));
        try {
        	facadeContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
	/**
     * @return true if the floor was added successfully
     */
    public boolean addFloor(String floor_name, int floor_number) {
    	Object id = floorContainer.addItem();
    	floorContainer.getContainerProperty(id, "floor_name").setValue(floor_name);
    	floorContainer.getContainerProperty(id, "floor_number").setValue(floor_number);
        try {
        	floorContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
	/**
     * @return true if the window was added successfully
     */
    public boolean addWindow(double head_height, double cill_height, double reveal, int facade_id, int floor_id) {
    	Object id = windowContainer.addItem();
    	windowContainer.getContainerProperty(id, "head_height").setValue(head_height);
    	windowContainer.getContainerProperty(id, "cill_height").setValue(cill_height);
    	windowContainer.getContainerProperty(id, "reveal").setValue(reveal);
    	windowContainer.getContainerProperty(id, "facade_id").setValue(facade_id);
    	windowContainer.getContainerProperty(id, "floor_id").setValue(floor_id);
        try {
        	windowContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
	/**
     * @return true if the user was added successfully
     */
    public boolean addUser(int company_id, String first_name, String second_name, String email) {
    	Object id = userContainer.addItem();
    	userContainer.getContainerProperty(id, "company_id").setValue(company_id);
    	userContainer.getContainerProperty(id, "first_name").setValue(first_name);
    	userContainer.getContainerProperty(id, "second_name").setValue(second_name);
    	userContainer.getContainerProperty(id, "email").setValue(email);
        try {
        	userContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
	/**
     * @return true if the authentication was added successfully
     */
    public boolean addAuthentication(int user_id, String username, String hash) {
    	Object id = authenticationContainer.addItem();
    	authenticationContainer.getContainerProperty(id, "user_id").setValue(user_id);
    	authenticationContainer.getContainerProperty(id, "username").setValue(username);
    	authenticationContainer.getContainerProperty(id, "hash").setValue(hash);
        try {
        	authenticationContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * Adds a new user building to the container and commits changes to the database.
     * 
     * @param user_id
     * @param building_id
     * 
     * @return true if the user building was added successfully
     */
    public boolean addUserBuilding(int user_id, int building_id) {
    	Object id = user_has_buildingContainer.addItem();
    	user_has_buildingContainer.getContainerProperty(id, "user_id").setValue(user_id);
    	user_has_buildingContainer.getContainerProperty(id, "building_id").setValue(building_id);
        try {
        	user_has_buildingContainer.commit();
            return true;
        } catch (UnsupportedOperationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public int createProject(int user_id, int company_id) {
    	
    	this.addProject(company_id, "New Project", "", "", -6.2661d, 53.3428d, null);
    	int project_id = newProjectID;
    	
    	this.addUserProject(user_id, project_id);
    	
    	this.addBuilding(null, null, 10, 10, 3, 9, 3, 2.5, NS, 0.2d, 0.3d, 0.2d, 1.5d, 0.3, null);
    	int building_id = newBuildingID;
    	
    	this.addProjectBuilding(project_id, building_id);
    	
    	this.addUserBuilding(user_id, building_id);
    	
    	int facade_id[] = new int[maxFacades];
    	int floor_id[] = new int[maxStoreys];
    	
    	for (int i = 0; i < maxFacades; i++) {
    		
    		this.addFacade((i + 1), 39.2, false, null, null, 15.0d, null, null, 0.0d, null, null, null, 0.0d, 0.0d);
    		facade_id[i] = newFacadeID;
    		
    		this.addBuildingFacade(building_id, facade_id[i]);
    	}
    	
    	for (int j = 0; j < maxStoreys; j++) {
    		
    		this.addFloor(floorNames[j], j);
    		floor_id[j] = newFloorID;
    		
    		this.addBuildingFloor(building_id, floor_id[j] );
    	}

    	for (int k = 0; k < maxFacades; k++) {
    		for (int l = 0; l < maxStoreys; l++) {
    			this.addWindow(2.5d, 1.2d, 0.1d, facade_id[k], floor_id[l]);
    		}
    	}
    	
    	return project_id;
    }
    
    public void createUser(int company_id, String first_name, String second_name, String email, String username, String password) {
    	
    	Filter filter = new Like("username", username, false);
    	authenticationContainer.addContainerFilter(filter);
    	Object id = authenticationContainer.getIdByIndex(0);
    	authenticationContainer.removeContainerFilter(filter);
    	
    	if (id == null) {
	    	this.addUser(company_id, first_name, second_name, email);
	    	int user_id = newUserID;
	    	
	    	String hash = null;
			try {
				hash = PasswordHelper.createHash(password);
			} catch (NoSuchAlgorithmException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvalidKeySpecException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	
	    	this.addAuthentication(user_id, username, hash);
	    	
    	} else {
    		System.out.println("Username " + username + " already exists");
    	}
    	
    }
    
    public boolean login(String username, String password) throws NoSuchAlgorithmException, InvalidKeySpecException {
    	boolean success = false;
    	
    	Filter filter = new Like("username", username, false);
    	authenticationContainer.addContainerFilter(filter);
    	Object id = authenticationContainer.getIdByIndex(0);
    	authenticationContainer.removeContainerFilter(filter);
    	  	 
    	if (id != null) {
    		String goodHash = (String) authenticationContainer.getItem(id).getItemProperty("hash").getValue();
            success = PasswordHelper.validatePassword(password, goodHash);
    	}
    	
    	return success;
    }
    

	public SQLContainer getCompanyContainer() {
		return companyContainer;
	}

	public SQLContainer getUserContainer() {
		return userContainer;
	}

	public SQLContainer getAuthenticationContainer() {
		return authenticationContainer;
	}

	public SQLContainer getProjectContainer() {
		return projectContainer;
	}

	public SQLContainer getLogin_historyContainer() {
		return login_historyContainer;
	}

	public SQLContainer getUser_has_projectContainer() {
		return user_has_projectContainer;
	}

	public SQLContainer getBuildingContainer() {
		return buildingContainer;
	}

	public SQLContainer getFacadeContainer() {
		return facadeContainer;
	}

	public SQLContainer getFloorContainer() {
		return floorContainer;
	}

	public SQLContainer getBuilding_has_facadeContainer() {
		return building_has_facadeContainer;
	}

	public SQLContainer getProject_has_buildingContainer() {
		return project_has_buildingContainer;
	}

	public SQLContainer getWindowContainer() {
		return windowContainer;
	}

	public SQLContainer getBuilding_has_floorContainer() {
		return building_has_floorContainer;
	}

	public SQLContainer getUser_has_buildingContainer() {
		return user_has_buildingContainer;
	}
}
